﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OverStore.Configuration;
using OverStore.Exceptions;
using System.Collections;
using OverStore.Configuration.Components;
using OverStore.Runtime.Storage;
using System.Reflection;
using OverStore.Configuration.Builder.Fluent;
using OverStore.Runtime.ObjectRepository.Mapping;
using OverStore.Runtime.ObjectRepository.CommandSet;

namespace OverStore.TutorialAsTest.Tutorial1.Lesson2
{
    /// <summary>
    /// In this lesson we consider object readers.
    /// Object reader is generic way to get sequence of 
    /// object from database.
    /// 
    /// We will use our MyMoney database for demonstrating 
    /// object reading capabilities.
    /// </summary>
    [TestClass]
    public class Lesson2
    {
        /****
         * Object Reader produces set of element. 
         * Order of element are not guaranteed (because database does 
         * not guarantee order of rows in query results without explicity order by clause).
         * 
         * Elements returned by reader should be conformed to identity mapping principe 
         * (if no special configuration provided for avoid this). This means that any 
         * element got using GetByIdentifier() with specified Id should be same instance as
         * object returned by reader.
         * 
         *  There are two way to configure object reader.
         *  First way is keyset reader: extract only keys from 
         *  database, and get entity by key in separate request. 
         *  
         *  Generally, this is significantly slower way, if you create reader of this 
         *  type and then iterate over it, but it 
         *  may be useful in following scenarious:
         *   * most of objects are already in session cache;
         *   * set of object is very large but actually a small amount 
         *     of object required right now (for example, display object list
         *     in data grid).
         *     
         * Secod way is resultset reader: each row contains full data 
         * required for construct object.         
         *     
         * We will use Item table from MyMoney database. Here is part of content of this table:
         * 
         * ID                                  OverrideDbType Name            CreatedBy                               CreatedDate         EditedBy                                EditedDate
         *-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
         * b7b0b51b-05ee-41a8-9085-6cd70fe91d1f	2	USD	        	c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00
         * d7c16782-12e1-4519-b15e-266fa60be0da	2	UAH	        	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * ed84a0c5-47c1-414c-b68b-69d338a3d830	1	Meat	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 8361e80f-76e5-4832-be7f-e55c761ba5c4	1	Bread	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 5136773a-eca9-4daf-a7a6-52ef443ef6d5	1	Milk	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * a0a9a28e-979f-483b-9467-b95cc41fe31c	1	Vegetables		af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 0fee8f08-51ec-4d44-81e8-c3ac2898340b	1	Bear	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 82f1492a-d4c8-4f46-a223-70d6e68a5da5	1	Viski	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * f81e87b0-4537-453f-9e70-26e58b361ee0	1	Absent	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * e718725c-9605-40d2-be06-1be41a51d9dc	1	Marlboro		af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 9838eef2-6c71-4fdd-9ea9-eea02e3fd723	1	Underwear		af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 8075e129-24e9-4833-9a53-f1cd58bd9fc7	1	Warm wear		af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 56c218ea-f396-48a4-a092-3b354b97efc0	1	Shorts	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 0570a74f-9c96-45c5-9924-e7597ec88991	1	Cinema	    	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 3879c67c-82a6-44b4-8492-85b645de2d4c	1	Bar	        	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00	af7989bb-1acb-4d00-a49a-03551c3857cc	26.05.2009 0:00:00
         * 
         *   
         * Few columns of this table are foreing keys to other tables, but now we ignore this fact.
         ****/

        class ItemView
        {
            public Guid Id { get; set; }

            public int Type { get; set; }

            public string Name { get; set; }

            public Guid CreatedBy { get; set; }

            public DateTime CreatedDate { get; set; }

            public Guid EditedBy { get; set; }

            public DateTime EditedDate { get; set; }
        }

        static IComponentConfiguration<IMapper<ItemView>> CreateItemViewMapper()
        {
            return Component.Mapper.Delegate<ItemView>(
                (item, name, p) =>
                {
                    if (name == "Id")
                        return item.Id;
                    if (name == "Type")
                        return item.Type;
                    if (name == "Name")
                        return item.Name;
                    if (name == "CreatedBy")
                        return item.CreatedBy;
                    if (name == "CreatedDate")
                        return item.CreatedDate;
                    if (name == "EditedBy")
                        return item.EditedBy;
                    if (name == "EditedDate")
                        return item.EditedDate;
                    if (name == "orig__Id")
                        return p.GetIdentifier<ItemView, Guid>(item);
                    return Missing.Value;
                },
                (data, item, p) =>
                {
                    item.Type = data.GetInt32("Type");
                    item.Name = data.GetString("Name");
                    item.CreatedBy = data.GetGuid("CreatedBy");
                    item.CreatedDate = data.GetDateTime("CreatedDate");
                    item.EditedBy = data.GetGuid("EditedBy");
                    item.EditedDate = data.GetDateTime("EditedDate");
                });
        }

        /// <summary>
        /// Create configuration contains repository for ItemView and
        /// keyset-based reader for ItemView.
        /// </summary>
        static OverStoreConfiguration CreateItemViewConfigWithKeysetReader(string connectionString)
        {
            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<ItemView, Guid>()
                    .WithDepot(Component.Depot.WithInternalKey<ItemView, Guid>("Id"))
                    .WithMapper(CreateItemViewMapper())
                    .CommandsFromTable(false, Component.CreateTable("Item")
                                        .Key<Guid>("Id")
                                        .Column<int>("Type")
                                        .Column<string>("Name")
                                        .Column<Guid>("CreatedBy")
                                        .Column<DateTime>("CreatedDate")
                                        .Column<Guid>("EditedBy")
                                        .Column<DateTime>("EditedDate"))
                    .ByIdentifierAttaching(i => i.Id)
                    .ParameterlessReader(false)
                        .WithCommand(Component.AdoCommand.ReadCommand("select id from Item"))
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                .EndObjectRepository()
            .End();
        }

        /// <summary>
        /// Check configuration.
        /// </summary>
        [TestMethod]
        public void ExecuteReaderItemViewWithKeysetTestConfig()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithKeysetReader(connectionString).CreateSession())
            {
                // Get 'Cinema' item.
                var cinema = session.GetByIdentifier<ItemView, Guid>(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"));
                Assert.AreEqual(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"), cinema.Id);
                Assert.AreEqual(1, cinema.Type);
                Assert.AreEqual("Cinema", cinema.Name);
                Assert.AreEqual(new DateTime(2009, 5, 26, 0, 0, 0), cinema.CreatedDate);
            }
        }

        /// <summary>
        /// Simple example of keyset based object reader. 
        /// </summary>
        [TestMethod]
        public void ExecuteReaderKeyset()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithKeysetReader(connectionString).CreateSession())
            {
                var reader = session.CreateReader<ItemView>().ToList();

                var cinema = session.GetByIdentifier<ItemView, Guid>(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"));
                Assert.AreEqual(15, reader.Count());
                Assert.IsTrue(reader.Single(i => ReferenceEquals(i, cinema)) != null);

                var reader2 = session.CreateReader<ItemView>().ToList();
                CollectionAssert.AreEquivalent(reader, reader2);
            }
        }

        /// <summary>
        /// Create configuration contains repository for ItemView and
        /// resultset-based reader for ItemView.
        /// </summary>
        static OverStoreConfiguration CreateItemViewConfigWithResultsetReader(string connectionString)
        {
            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<ItemView, Guid>()
                    .WithDepot(Component.Depot.WithInternalKey<ItemView, Guid>("Id"))
                    .WithMapper(CreateItemViewMapper())
                    .CommandsFromTable(false, Component.CreateTable("Item")
                                        .Key<Guid>("Id")
                                        .Column<int>("Type")
                                        .Column<string>("Name")
                                        .Column<Guid>("CreatedBy")
                                        .Column<DateTime>("CreatedDate")
                                        .Column<Guid>("EditedBy")
                                        .Column<DateTime>("EditedDate"))
                    .ByIdentifierAttaching(i => i.Id)
                    .ParameterlessReader(false)
                        .WithCommand(Component.AdoCommand.ReadCommand("select Id, Type, Name, CreatedBy, CreatedDate, EditedBy, EditedDate from Item"))
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                .EndObjectRepository()
            .End();
        }

        /// <summary>
        /// Test our configuration produced by CreateItemViewConfigWithResultsetReader method
        /// for correctness.
        /// </summary>
        [TestMethod]
        public void ExecuteReaderItemViewWithResultsetTestConfig()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithResultsetReader(connectionString).CreateSession())
            {
                // Get 'Cinema' item
                var cinema = session.GetByIdentifier<ItemView, Guid>(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"));
                Assert.AreEqual(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"), cinema.Id);
                Assert.AreEqual(1, cinema.Type);
                Assert.AreEqual("Cinema", cinema.Name);
                Assert.AreEqual(new DateTime(2009, 5, 26, 0, 0, 0), cinema.CreatedDate);
            }
        }

        /// <summary>
        /// Simple example of resultset based object reader. 
        /// </summary>
        [TestMethod]
        public void ExecuteReaderResultset()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithResultsetReader(connectionString).CreateSession())
            {
                var reader = session.CreateReader<ItemView>().ToList();

                var cinema = session.GetByIdentifier<ItemView, Guid>(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"));
                Assert.AreEqual(15, reader.Count());
                Assert.IsTrue(reader.Single(i => ReferenceEquals(i, cinema)) != null);
                var reader2 = session.CreateReader<ItemView>().ToList();
                CollectionAssert.AreEquivalent(reader, reader2);
            }
        }

        /// <summary>
        /// Every object reader is smart wrapper over IDataReader (or other forward-only 
        /// non-reusable sequential data accessor). We cant iterate same IDataReader instance 
        /// twise or more times.
        /// 
        /// This is very important example demonstrates reusability of object reader.
        /// During execution this example reader calls two times:
        /// First time - for get Count() result.
        /// Second time - for get Id collection in last statement.
        /// 
        /// You can see this if look into log.
        /// </summary>
        [TestMethod]
        public void ExecuteReaderApplyLinqToObjects()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithResultsetReader(connectionString).CreateSession())
            {
                // This expression does not causes reader to access database.
                var processedReader = session.CreateReader<ItemView>()
                        .OrderBy(v => v.Name)
                        .Reverse()
                        .Skip(2)
                        .Take(5)
                        .Where(v => v.Name.IndexOf("a", StringComparison.OrdinalIgnoreCase) != -1);

                var ids = new[] 
            { 
                new Guid("a0a9a28e-979f-483b-9467-b95cc41fe31c"),
                new Guid("9838eef2-6c71-4fdd-9ea9-eea02e3fd723"),
                new Guid("d7c16782-12e1-4519-b15e-266fa60be0da")
            };

                // Enumerable.Count() causes reader access database at first time.
                // Because of deffered execution, select 
                Assert.AreEqual(ids.Length, processedReader.Count());
                // Enumerable.Select() causes execution of combined construction consist of 
                // OrderBy, Reverse, Skip, Take, Where and Select.
                CollectionAssert.AreEqual(ids.ToList(), processedReader.Select(iv => iv.Id).ToList());
            }
        }

        /// <summary>
        /// Filter for object reader. 
        /// </summary>
        class ItemViewReadParams
        {
            public int Id { get; set; }

            public string NameLike { get; set; }

            public Guid? CreatedByFilter { get; set; }

            public DateTime? CreatedDateFilter { get; set; }
        }

        static IComponentConfiguration<IMapper<ItemViewReadParams>> CreateItemViewReadParamsMapper()
        {
            return Component.Mapper.Reflection<ItemViewReadParams>();
        }

        static OverStoreConfiguration CreateItemViewConfigWithParameterizedReaderForced(string connectionString)
        {
            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<ItemView, Guid>()
                    .WithDepot(Component.Depot.WithInternalKey<ItemView, Guid>("Id"))
                    .WithMapper(CreateItemViewMapper())
                    .WithCommands(CreateItemViewCommandSet())
                    .ByIdentifierAttaching(i => i.Id)
                    .ParameterlessReader(false)
                        .WithCommand(Component.AdoCommand.ReadCommand("select id from Item"))
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                    .ParameterizedReader<ItemViewReadParams>(true)
                        .ReadCommand()
                            .UseSqlCommand("select Id, Type, Name, CreatedBy, CreatedDate, EditedBy, EditedDate from Item " +
                                           "where Name like @NameLike AND " +
                                           " (CreatedBy = @CreatedByFilter or @CreatedByFilter is null) AND " +
                                           " (CreatedDate = @CreatedDateFilter OR @CreatedDateFilter is null)")
                            .NullableInputParameter("NameLike", DbType.String)
                            .NullableInputParameter("CreatedByFilter", DbType.Guid)
                            .NullableInputParameter("CreatedDateFilter", DbType.DateTime)
                        .EndCommand()
                        .DelegateMapper((item, name, p) =>
                            {
                                if (name == "Id")
                                    return item.Id;
                                if (name == "NameLike")
                                    return item.NameLike;
                                if (name == "CreatedByFilter")
                                    return item.CreatedByFilter;
                                if (name == "CreatedDateFilter")
                                    return item.CreatedDateFilter;
                                return Missing.Value;
                            })
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                .EndObjectRepository()
            .End();
        }

        private static OverStore.Configuration.ObjectRepository.CommandSet.TableCommandSetConfiguration CreateItemViewCommandSet()
        {
            return Component.CommandSet.FromTable(
                       Component.CreateTable("Item")
                                .Key<Guid>("Id")
                                .Column<int>("Type")
                                .Column<string>("Name")
                                .Column<Guid>("CreatedBy")
                                .Column<DateTime>("CreatedDate")
                                .Column<Guid>("EditedBy")
                                .Column<DateTime>("EditedDate"), true);
        }

        /// <summary>
        /// Create configuration contains repository for ItemView and
        /// resultset-based reader for ItemView.
        /// </summary>
        static OverStoreConfiguration CreateItemViewConfigWithParameterizedReader(string connectionString)
        {
            return CreateItemViewConfigWithParameterizedReaderForced(connectionString);
        }

        /// <summary>
        /// Repeat our standard test for check configuration.
        /// </summary>
        [TestMethod]
        public void ExecuteReaderParameterizedReaderTestConfig()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithParameterizedReader(connectionString).CreateSession())
            {
                // Get 'Cinema' item.
                var cinema = session.GetByIdentifier<ItemView, Guid>(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"));
                Assert.AreEqual(new Guid("0570a74f-9c96-45c5-9924-e7597ec88991"), cinema.Id);
                Assert.AreEqual(1, cinema.Type);
                Assert.AreEqual("Cinema", cinema.Name);
                Assert.AreEqual(new DateTime(2009, 5, 26, 0, 0, 0), cinema.CreatedDate);
            }
        }

        [TestMethod]
        public void ExecuteReaderParameterized()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithParameterizedReader(connectionString).CreateSession())
            {
                var parameters = new ItemViewReadParams
                {
                    NameLike = "%ar%"
                };

                var ids = new[] 
            {
                new Guid("0fee8f08-51ec-4d44-81e8-c3ac2898340b"),
                new Guid("e718725c-9605-40d2-be06-1be41a51d9dc"),
                new Guid("9838eef2-6c71-4fdd-9ea9-eea02e3fd723"),
                new Guid("8075e129-24e9-4833-9a53-f1cd58bd9fc7"),
                new Guid("3879c67c-82a6-44b4-8492-85b645de2d4c")
            };

                var reader = session.CreateReader<ItemView, ItemViewReadParams>(parameters).ToList();
                Assert.AreEqual(5, reader.Count);

                CollectionAssert.AreEquivalent(ids.ToList(), reader.Select(i => i.Id).ToList());
            }
        }

        /****
         * Some test of edge conditions and exceptions
         ****/

        [TestMethod]
        [ExpectedException(typeof(TypePersistenceNotSupportedException))]
        public void ExecuteReaderUnknownItemType()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithKeysetReader(connectionString).CreateSession())
                session.CreateReader<string>();
        }

        [TestMethod]
        [ExpectedException(typeof(TypePersistenceNotSupportedException))]
        public void ExecuteReaderUnknownItemType2()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithKeysetReader(connectionString).CreateSession())
                session.CreateReader<string, string>("no");
        }

        [TestMethod]
        [ExpectedException(typeof(ParameterTypeNotSupportedException))]
        public void ExecuteReaderUnknownParameterType()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithKeysetReader(connectionString).CreateSession())
                // No parameterized commands defined for reader.
                session.CreateReader<ItemView, ItemViewReadParams>(new ItemViewReadParams());
        }

        [TestMethod]
        [ExpectedException(typeof(ParameterTypeNotSupportedException))]
        public void ExecuteReaderUnknownParameterType2()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithParameterizedReader(connectionString).CreateSession())
                // No parameterized commands with parameter type 'String' defined.
                session.CreateReader<ItemView, string>("no");
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentNullException))]
        public void ExecuteReaderNullParameters()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithParameterizedReader(connectionString).CreateSession())
                session.CreateReader<ItemView, ItemViewReadParams>(null);
        }

        [TestMethod]
        [ExpectedException(typeof(ObjectDisposedException))]
        public void ExecuteReaderDisposedSession()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            var session = CreateItemViewConfigWithKeysetReader(connectionString).CreateSession();
            session.Dispose();
            session.CreateReader<ItemView>();
        }

        [TestMethod]
        [ExpectedException(typeof(ObjectDisposedException))]
        public void ExecuteReaderDisposedSession2()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            var session = CreateItemViewConfigWithParameterizedReader(connectionString).CreateSession();
            session.Dispose();
            session.CreateReader<ItemView, ItemViewReadParams>(new ItemViewReadParams());
        }

        /// <summary>
        /// Create configuration contains repository for ItemView and
        /// keyset-based reader for ItemView.
        /// </summary>
        static OverStoreConfiguration CreateItemViewReaderWithInvalidSql(string connectionString)
        {
            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<ItemView, Guid>()
                    .WithDepot(Component.Depot.WithInternalKey<ItemView, Guid>("Id"))
                    .WithMapper(CreateItemViewMapper())
                    .WithCommands(CreateItemViewCommandSet())
                    .WithChangesTracker(Component.InstanceChangesTracker.Checksum<ItemView, Guid>("Id", "Type", "Name", "CreatedBy", "CreatedDate", "EditedBy", "EditedDate"))
                    .ByIdentifierAttaching(i => i.Id)
                       .ParameterlessReader(false)
                        .WithCommand(Component.AdoCommand.ReadCommand("select not_exists from Item"))
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                .EndObjectRepository()
            .End();
        }

        [TestMethod]
        [ExpectedException(typeof(PersistenceOperationFailedException))]
        public void ExecuteReaderPersistingOperationFailed()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewReaderWithInvalidSql(connectionString).CreateSession())
                session.CreateReader<ItemView>().Count();
        }

        static OverStoreConfiguration CreateItemViewConfigWithParameterizedReaderInvalidSql(string connectionString)
        {
            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<ItemView, Guid>()
                    .WithDepot(Component.Depot.WithInternalKey<ItemView, Guid>("Id"))
                    .WithMapper(CreateItemViewMapper())
                    .WithCommands(CreateItemViewCommandSet())
                    .ByIdentifierAttaching(i => i.Id)
                    .ParameterlessReader(true)
                        .WithCommand(Component.AdoCommand.ReadCommand("select Id, Type, Name, CreatedBy, CreatedDate, EditedBy, EditedDate from Item"))
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                    .ParameterizedReader<ItemViewReadParams>(true)
                        .ReadCommand()
                            .UseSqlCommand("select not_exists, Type, Name, CreatedBy, CreatedDate, EditedBy, EditedDate from Item " +
                                           "where Name like @NameLike AND " +
                                           " (CreatedBy = @CreatedByFilter or @CreatedByFilter is null) AND " +
                                           " (CreatedDate = @CreatedDateFilter OR @CreatedDateFilter is null)")
                            .NullableInputParameter("NameLike", DbType.String)
                            .NullableInputParameter("CreatedByFilter", DbType.Guid)
                            .NullableInputParameter("CreatedDateFilter", DbType.DateTime)
                         .EndCommand()
                        .WithParametersMapper(CreateItemViewReadParamsMapper())
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<Guid>(r => r.GetGuid("Id")))
                    .EndReader()
                .EndObjectRepository()
            .End();
        }

        [TestMethod]
        [ExpectedException(typeof(PersistenceOperationFailedException))]
        public void ExecuteReaderPersistingOperationFailedParameterizedReader()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreateItemViewConfigWithParameterizedReaderInvalidSql(connectionString).CreateSession())
                session.CreateReader<ItemView, ItemViewReadParams>(new ItemViewReadParams()).Count();
        }

        /* *******
         * OverrideDbType Name        Description
         * -----------------------------------------------------------------------
         * 1	Purse	    Place to store money: card, bank account, purse itself.
         * 2	Account	    Account to pay to or get payment from: bank account, booking, bank terminal.
         * 3	Warehouse	Place to store products: warehouse itself, home. Product can be placed in and got from at any time.
         * 4	Shop	    Place to buy or sale product. Also for recycle old or bad products. 
         * 
         * *******/

        static readonly ICollection PlaceTypeOrigin =
            new[]
            {
                new PlaceType { Type = 1, Name = "Purse",       Description = "Place to store money: card, bank account, purse itself."},
                new PlaceType { Type = 2, Name = "Account",     Description = "Account to pay to or get payment from: bank account, booking, bank terminal."},
                new PlaceType { Type = 3, Name = "Warehouse",   Description = "Place to store products: warehouse itself, home. Product can be placed in and got from at any time."},
                new PlaceType { Type = 4, Name = "Shop",        Description = "Place to buy or sale product. Also for recycle old or bad products."}
            };

        class PlaceType
        {
            public int Type { get; set; }

            public string Name { get; set; }

            public string Description { get; set; }

            public override bool Equals(object obj)
            {
                var pt = obj as PlaceType;
                if (pt == null)
                    return false;
                return Type == pt.Type &&
                       (Name ?? String.Empty).Trim().Equals((pt.Name ?? String.Empty).Trim()) &&
                       (Description ?? String.Empty).Trim().Equals((pt.Description ?? String.Empty).Trim());
            }

            public override int GetHashCode()
            {
                return Type;
            }
        }

        static IComponentConfiguration<ICommandSet> CreatePlaceTypeCommandSet()
        {
            return Component.CommandSet.FromTable(
                        Component.CreateTable("PlaceType")
                                            .Key<int>("Type")
                                            .Column<String>("Name")
                                            .Column<String>("Description"),
                        false);
        }

        static IComponentConfiguration<IMapper<PlaceType>> CreatePlaceTypeMapper()
        {
            return Component.Mapper.Delegate<PlaceType>(
                (item, name, p) =>
                {
                    if (name == "Type")
                        return item.Type;
                    if (name == "Name")
                        return item.Name;
                    if (name == "Description")
                        return item.Description;
                    if (name == "orig__Type")
                        return p.GetIdentifier<PlaceType, int>(item);
                    return Missing.Value;
                },
                (data, item, p) =>
                {
                    item.Name = data.GetString("Name");
                    item.Description = data.GetString("Description");
                });
        }

        static OverStoreConfiguration CreatePlaceTypeStorage(string connectionString, bool useResultsetForObjectConstruction)
        {
            return OverStoreFluent.Configure()
                .NoLogging()
                .ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<PlaceType, int>()
                    .WithDepot(Component.Depot.WithInternalKey<PlaceType, int>("Type"))
                    .WithMapper(CreatePlaceTypeMapper())
                    .WithCommands(CreatePlaceTypeCommandSet())
                    .ByIdentifierAttaching(i => i.Type)
                    .ParameterlessReader(useResultsetForObjectConstruction)
                        .WithCommand(Component.AdoCommand.ReadCommand("select * from PlaceType"))
                        .WithIdentifierProvider(Component.IdentifierProvider.Delegate<int>(r => r.GetInt32("Type")))
                    .EndReader()
                .EndObjectRepository()
            .End();
        }

        [TestMethod]
        public void ExecuteReaderCheckContentResultset()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreatePlaceTypeStorage(connectionString, true).CreateSession())
            {
                var actual = session.CreateReader<PlaceType>().ToArray();
                CollectionAssert.AreEqual(PlaceTypeOrigin, actual);
            }
        }

        [TestMethod]
        public void ExecuteReaderCheckContentKeyset()
        {
            var connectionString = TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
            using (var session = CreatePlaceTypeStorage(connectionString, false).CreateSession())
            {
                var actual = session.CreateReader<PlaceType>().ToArray();
                CollectionAssert.AreEqual(PlaceTypeOrigin, actual);
            }
        }
    }
}
